﻿using AutoMapper;
using DeZhen.DataCollection.IRepository.CollectionInstance;
using DeZhen.DataCollection.IRepository.CollectionTask;
using DeZhen.DataCollection.IRepository.CollectionTemplate;
using DeZhen.DataCollection.IRepository.DataSourceSetting;
using DeZhen.DataCollection.IService.CollectionTask;
using DeZhen.DataCollection.Model.DTO.CollectionTask;
using DeZhen.DataCollection.Model.Entities;
using DeZhen.DataCollection.Model.Enums;
using Microsoft.Extensions.Configuration;
using SqlSugar;
using System.Data;
using Yitter.IdGenerator;
using Hangfire;
using Serilog;

namespace DeZhen.DataCollection.Service.CollectionTask
{
    public class CollectionTaskService : ICollectionTaskService
    {
        private readonly ICollectionInstanceRepository _instancesRepository;
        private readonly ICollectionInstanceDetailRepository _instanceDetailRepository;
        private readonly ICollectionTaskRepository _taskRepository;
        private readonly ICollectionTaskDetailRepository _taskDetailRepository;
        private readonly ISqlSugarClient _db;
        private readonly ICollectionTemplateRepository _templateRepository;
        private readonly ICollectionTemplateDetailRepository _templateDetailRepository;
        private readonly IDataSourceSettingsRepository _settingsRepository;
        private readonly IConfiguration _configuration;
        private readonly IMapper _mapper;
        private readonly IBackgroundJobClient _backgroundJobClient;
        private readonly ILogger _logger;

        public CollectionTaskService(ICollectionInstanceRepository instancesRepository,
            ICollectionTaskRepository taskRepository,
            ISqlSugarClient db,
            ICollectionInstanceDetailRepository instanceDetailRepository,
            ICollectionTemplateRepository templateRepository,
            ICollectionTemplateDetailRepository templateDetailRepository,
            IDataSourceSettingsRepository settingsRepository,
            IConfiguration configuration,
            ICollectionTaskDetailRepository taskDetailRepository,
            IMapper mapper,
            IBackgroundJobClient backgroundJobClient,
            ILogger logger)
        {
            _instancesRepository = instancesRepository;
            _taskRepository = taskRepository;
            _db = db;
            _instanceDetailRepository = instanceDetailRepository;
            _templateRepository = templateRepository;
            _templateDetailRepository = templateDetailRepository;
            _settingsRepository = settingsRepository;
            _configuration = configuration;
            _taskDetailRepository = taskDetailRepository;
            _mapper = mapper;
            _backgroundJobClient = backgroundJobClient;
            _logger = logger;
        }

        public async Task<long?> SaveCollectionTaskAsync(SaveCollectionTask input)
        {
            if (input == null || string.IsNullOrEmpty(input.TaskName))
                return null;

            var queryConditionList = await GetQueryConditionsAsync(input);

            var instanceModel = await _instancesRepository.GetLatestInstanceAsync(input.CollectionType);

            // 采集任务表写入数据  
            var taskId = YitIdHelper.NextId();
            var taskEntity = new CollectionTasks
            {
                Id = taskId,
                CollectionInstanceId = instanceModel.Id,
                DataSourceSettingsId = input.DataSourceSettingsId,
                TaskName = input.TaskName,
                CreateTime = DateTime.Now,
            };

            try
            {
                //执行远程数据同步，并向采集任务明细表写入数据  
                //await SyncRemoteDataAsync(taskId, input.DataSourceSettingsId, queryConditionList);
                await _taskRepository.CreateAsync(taskEntity);

                // 调用Hangfire后台任务
                _backgroundJobClient.Enqueue<CollectionTaskService>(service =>
                    service.ExecuteCollectionTaskAsync(taskId, input.DataSourceSettingsId, queryConditionList));


            }
            catch (Exception ex)
            {
                _logger.Error($"保存采集任务失败: {ex.Message}");
                return null;
            }

            return taskId;
        }

        public async Task<List<CollectionTaskDetailDTO>> GetTaskDetailByTaskId(long taskId)
        {
            var taskDetailList = await _taskDetailRepository.GetListByTaskIdAsync(taskId);
            var result = _mapper.Map<List<CollectionTaskDetailDTO>>(taskDetailList);

            return result;
        }

        public async Task ExecuteCollectionTaskAsync(long taskId, long dataSourceSettingsId, Dictionary<string, string> queryConditionList)
        {
            await SyncRemoteDataAsync(taskId, dataSourceSettingsId, queryConditionList);
        }


        #region Private Function

        /// <summary>
        /// 获取查询条件
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private async Task<Dictionary<string, string>> GetQueryConditionsAsync(SaveCollectionTask input)
        {
            Dictionary<string, string> exectueList = new Dictionary<string, string>();

            var instanceModel = await _instancesRepository.GetLatestInstanceAsync(input.CollectionType);
            if (instanceModel == null)
                throw new Exception("采集实例为空");

            var instanceDetails = await _instanceDetailRepository.GetDetailByInstanceIdAsync(instanceModel.Id);
            if (instanceDetails == null || !instanceDetails.Any())
                throw new Exception("采集实例明细为空");

            bool hasIDCard = !string.IsNullOrWhiteSpace(input.IDCardList);
            bool hasDate = input.BeginDate != null && input.EndDate != null;


            // 1. 按 CollectionTemplateId 分组
            var groupList = instanceDetails
                            .Where(x => x.CollectionTemplateId.HasValue)
                            .GroupBy(x => x.CollectionTemplateId.Value)
                            .ToList();

            // 2. 遍历分组
            foreach (var group in groupList)
            {
                var collectionTemplateId = group.Key;
                var templateModel = await _templateRepository.GetByIdAsync(collectionTemplateId);

                // 3. 查询该 CollectionTemplateId 下的所有明细
                var detailList = (await _instanceDetailRepository.GetListAsync())
                                .Where(x => x.CollectionInstanceId == instanceModel.Id)
                                .Where(x => x.CollectionTemplateId == collectionTemplateId)
                                .ToList();


                string sqlWhere = "";
                var whereList = new List<string>();
                foreach (var item in detailList)
                {
                    var detailModel = await _templateDetailRepository.GetByIdAsync(item.CollectionTemplateDetailId.Value);

                    if (item.ColumnType == BusinessColumnType.DateColumn)
                    {
                        // 4.1 日期条件
                        if (hasDate)
                        {
                            whereList.Add($"{detailModel.ColumnName} between '{input.BeginDate:yyyy-MM-dd}' and '{input.EndDate:yyyy-MM-dd}'");
                        }
                    }
                    else
                    {
                        // 4.2 身份证条件
                        if (hasIDCard)
                        {
                            var idCards = input.IDCardList.Split(',', StringSplitOptions.RemoveEmptyEntries)
                                                     .Select(x => $"'{x.Trim()}'");
                            whereList.Add($"{detailModel.ColumnName} in ({string.Join(",", idCards)})");
                        }
                    }

                    sqlWhere = whereList.Any() ? " where " + string.Join(" and ", whereList) : string.Empty;
                }

                exectueList.Add(templateModel.TableName, sqlWhere);
            }

            return exectueList;
        }

        /// <summary>
        /// 同步远程数据
        /// </summary>
        /// <param name="dataSourceSettingsId">数据源设置ID</param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        /// <exception cref="NotSupportedException"></exception>
        private async Task SyncRemoteDataAsync(long taskId, long dataSourceSettingsId, Dictionary<string, string> tableQueryConditions)
        {
            var settingsModel = await _settingsRepository.GetByIdAsync(dataSourceSettingsId);
            if (settingsModel == null) 
            {
                _logger.Error("数据源设置为空");
                throw new Exception("数据源设置为空");
            }
               

            // 在需要的地方读取conn_mysql字符串
            string localConn = _configuration.GetSection("ConnectionStrings")["connStr"];
            string localDbType = _configuration.GetSection("ConnectionStrings")["dbType"];

            string remoteConn;
            SqlSugar.DbType dbType = settingsModel.SourceType;
            switch (settingsModel.SourceType)
            {
                case SqlSugar.DbType.SqlServer:
                    remoteConn = $"Server={settingsModel.DatabaseUrl};Database={settingsModel.DatabaseName};User ID={settingsModel.LoginUser};Password={settingsModel.LoginPassword};TrustServerCertificate=true;";
                    break;
                case SqlSugar.DbType.MySql:
                    remoteConn = $"Server={settingsModel.DatabaseUrl};Port={settingsModel.Port};Database={settingsModel.DatabaseName};Uid={settingsModel.LoginUser};Pwd={settingsModel.LoginPassword};Charset=utf8mb4;AllowPublicKeyRetrieval=true;SslMode=None;AllowLoadLocalInfile=true;";
                    break;
                case SqlSugar.DbType.Oracle:
                    remoteConn = $"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={settingsModel.DatabaseUrl})(PORT={settingsModel.Port})))(CONNECT_DATA=(SERVICE_NAME={settingsModel.DatabaseName})));User Id={settingsModel.LoginUser};Password={settingsModel.LoginPassword};";
                    break;
                default:
                    throw new NotSupportedException($"暂不支持的数据库类型: {dbType}");
            }

            if (string.IsNullOrEmpty(localConn) || string.IsNullOrEmpty(remoteConn))
            {
                throw new Exception("数据库连接字符串为空");
            }

            var remoteConfig = new ConnectionConfig
            {
                ConnectionString = remoteConn,
                DbType = dbType,
                IsAutoCloseConnection = true
            };

            var localConfig = new ConnectionConfig
            {
                ConnectionString = localConn,
                DbType = (SqlSugar.DbType)Convert.ToInt32(localDbType),
                IsAutoCloseConnection = true
            };

            // 连接远程数据库
            using (var remoteDb = new SqlSugarScope(remoteConfig))
            {
                // 连接本地数据库
                using (var localDb = new SqlSugarScope(localConfig))
                {
                    foreach (var tableInfo in tableQueryConditions)
                    {
                        var tableName = tableInfo.Key;
                        var condition = tableInfo.Value;

                        _logger.Information($"开始同步表: {tableName}");

                        try
                        {
                            // 同步单个表
                            var totalCount = await SyncTable(remoteDb, localDb, tableName, condition);
                            await SaveTaskDetail(taskId, tableName, condition, totalCount);
                            //Console.WriteLine($"表 {tableName} 同步完成");
                            _logger.Information($"表 {tableName} 同步完成，影响行数: {totalCount}");
                        }
                        catch (Exception ex)
                        {
                            //Console.WriteLine($"表 {tableName} 同步失败: {ex.Message}");
                            _logger.Error($"表 {tableName} 同步失败: {ex.Message}");
                        }
                    }

                    //Console.WriteLine($"全部数据表同步完成，成功同步{tableQueryConditions.Count}张表数据");
                    _logger.Information($"全部数据表同步完成，成功同步{tableQueryConditions.Count}张表数据");
                }
            }
        }

        private async Task<int> SyncTable(SqlSugarScope remoteDb, SqlSugarScope localDb, string tableName, string condition)
        {
            // 获取远程表结构
            var tableInfo = remoteDb.DbMaintenance.GetTableInfoList(false);
            var targetTable = tableInfo.Find(t => t.Name.Equals(tableName, StringComparison.OrdinalIgnoreCase));
            if (targetTable == null)
                throw new Exception($"远程数据库中不存在表: {tableName}");

            // 获取列信息和主键信息
            var columnInfos = remoteDb.DbMaintenance.GetColumnInfosByTableName(tableName);
            var primaryKeys = remoteDb.DbMaintenance.GetPrimaries(tableName);
            if (primaryKeys == null || primaryKeys.Count == 0) 
            {
                _logger.Error($"表 {tableName} 未找到主键，无法进行按主键删除操作");
                throw new Exception($"表 {tableName} 未找到主键，无法进行按主键删除操作");
            }
               

            // 创建本地表（如不存在）
            if (!localDb.DbMaintenance.IsAnyTable(tableName))
            {
                localDb.DbMaintenance.CreateTable(tableName, columnInfos, true);
                //Console.WriteLine($"已创建本地表: {tableName}");
                _logger.Information($"已创建本地表: {tableName}");
            }

            int pageSize = 1000;
            int pageIndex = 1;
            int totalCount = 0;
            var dbType = remoteDb.CurrentConnectionConfig.DbType;

            while (true)
            {
                string sqlStr;
                if (dbType == SqlSugar.DbType.SqlServer)
                {
                    sqlStr = $"SELECT * FROM {tableName} {(string.IsNullOrWhiteSpace(condition) ? "" : condition)} ORDER BY (SELECT NULL) OFFSET {(pageIndex - 1) * pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY";
                }
                else
                {
                    sqlStr = $"SELECT * FROM {tableName} {(string.IsNullOrWhiteSpace(condition) ? "" : condition)} LIMIT {pageSize} OFFSET {(pageIndex - 1) * pageSize}";
                }

                var dataTable = await remoteDb.Ado.GetDataTableAsync(sqlStr);

                if (dataTable == null || dataTable.Rows.Count == 0)
                    break;

                // 1. 提取主键值
                var pkName = primaryKeys[0]; // 只支持单主键，如有多主键可扩展
                var pkType = dataTable.Columns[pkName].DataType;
                var pkValues = dataTable.AsEnumerable()
                    .Select(row => row[pkName])
                    .Where(val => val != DBNull.Value)
                    .ToList();

                if (pkValues.Count > 0)
                {
                    // 2. 构造主键IN语句，防注入
                    string inClause;
                    if (pkType == typeof(string) || pkType == typeof(DateTime))
                    {
                        inClause = string.Join(",", pkValues.Select(v => $"'{v.ToString().Replace("'", "''")}'"));
                    }
                    else
                    {
                        inClause = string.Join(",", pkValues);
                    }
                    var deleteSql = $"DELETE FROM {tableName} WHERE {pkName} IN ({inClause})";
                    await localDb.Ado.ExecuteCommandAsync(deleteSql);
                }

                // 3. 插入新数据
                int inserted = await localDb.Fastest<DataTable>().AS(tableName).BulkCopyAsync(dataTable);
                totalCount += inserted;
                pageIndex++;
            }

            return totalCount;
        }

        private async Task SaveTaskDetail(long taskId, string tableName, string sqlWhere, int affectRows)
        {
            var templateModel = (await _templateRepository.GetListAsync(x => x.TableName == tableName)).FirstOrDefault();
            if (templateModel == null)
                throw new Exception("业务表名称不存在");


            var taskDetailEntity = new CollectionTaskDetail
            {
                Id = YitIdHelper.NextId(),
                CollectionTaskId = taskId,
                TableName = tableName,
                TableCnName = templateModel.TableCnName,
                ExecuteSql = $"SELECT * FROM {tableName} {sqlWhere}",
                ExcuteConsume = 0, // 执行耗时可以在实际执行后更新
                AffectRows = affectRows,
                Status = ExecuteStatusEnum.Success,
                CreateTime = DateTime.Now
            };

            await _taskDetailRepository.CreateAsync(taskDetailEntity);
        }

        #endregion
    }
}
